 GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMarkers]') AND type in (N'P', N'PC'))
BEGIN
	DROP PROCEDURE [dbo].[GetMarkers]
END
GO

CREATE PROCEDURE [dbo].[GetMarkers]
(
	@gameName nvarchar(50),
	@login nvarchar(50),
	@password nvarchar(50)
)
AS
BEGIN

	-- check login and password
	DECLARE @teamID int
	SELECT 
		@teamID = TeamID
	FROM
		dbo.[User]
	WHERE
		[Login] = @login AND Password = @password
	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR (N'Login and Password are incorrect', 13, 1)
		RETURN -1
	END

	-- check last game
	IF NOT EXISTS 
		(
		SELECT * FROM dbo.Game
		WHERE [Name]=@gameName AND TeamID=@teamID)
	BEGIN
		RAISERROR (N'User ''%s''does not belong to game ''%s''', 13, 1, @login, @gameName)
		RETURN -1
	END

	-- get all information about makers for game
	SELECT
		P.EntityName, 
		P.GameName, 
		P.Latitude, 
		P.Longitude, 
		P.Altitude, 
		P.Speed,
		Datediff(s,P.[Time], GetDate()) Delay
	FROM dbo.GPSPoint P
	JOIN (
			-- get last point for marker and game
			SELECT EntityName, GameName, MAX([Time]) [Time] 
				FROM GPSPoint 
				WHERE GPSPoint.GameName = @gameName 
				GROUP BY EntityName, GameName
		) LP ON P.EntityName = LP.EntityName 
			AND P.GameName = LP.GameName
			AND P.[Time] = LP.[Time]
	WHERE 
		P.GameName = @gameName
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPoint]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[AddPoint]
END
GO

CREATE PROCEDURE [dbo].[AddPoint]
(
	@login nvarchar(50),
	@password nvarchar(50),
	@entityName nvarchar(50),
	@gameName nvarchar(50),
	@latitude float,
	@longitude  float,
	@altitude float = NULL,
	@speed int = NULL
)
AS
BEGIN
	BEGIN TRAN
	-- Insert new value in [GPSPoint]
	INSERT INTO [dbo].[GPSPoint]
           ([EntityName]
           ,[GameName]
           ,[Login]
           ,[Latitude]
           ,[Longitude]
           ,[Speed]
           ,[Altitude]
		   ,[UserID]
		   ,GameEntityID)
	-- check for existing User
	SELECT 
			@EntityName,
			@gameName,
			@login,
			@latitude,
			@longitude,
			@speed,
			@altitude,
			[User].ID,
			[GameEntity].ID
	FROM [dbo].[User]
	CROSS JOIN ([GameEntity] JOIN [Entity] ON [GameEntity].EntityID = [Entity].ID AND [Entity].Name = @entityName )
	WHERE
		[Login]=@login AND [Password]=@password

	-- check result
	IF @@ROWCOUNT = 0
	BEGIN
		ROLLBACK
		RAISERROR (N'Login and Password are incorrect', 13, 1)
		RETURN -1
	END

	-- get last ID
	SELECT SCOPE_IDENTITY()
	COMMIT

END
GO


/*IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPointHashed]') AND type in (N'P', N'PC'))
BEGIN
	DROP PROCEDURE [dbo].[AddPointHashed]
END
GO

CREATE PROCEDURE [dbo].[AddPointHashed]
(
	@hash datetime,
	@latitude float,
	@longitude  float,
	@altitude float = NULL,
	@speed int = NULL
)
AS
BEGIN
	BEGIN TRAN

	DECLARE @currentdate datetime
	SELECT @currentdate=getdate()

	-- Insert new value in [GPSPoint]
	INSERT INTO [dbo].[GPSPoint]
           ([EntityName]
           ,[GameName]
           ,[Login]
           ,[Latitude]
           ,[Longitude]
           ,[Speed]
           ,[Altitude]
			,[UserID])
	-- check for existing hash
	SELECT
		[EntityName],
		[GameName],
		[MarkerHash].[Login],
		@latitude, 
		@longitude, 
		@speed, 
		@altitude,
		[User].ID
	FROM
		[dbo].[MarkerHash]
	INNER JOIN
		[dbo].[User] ON [MarkerHash].[Login] = [User].[Login]
	WHERE
		[Hash] = @hash AND DATEDIFF(day, [Hash], @currentdate)<1

	-- check result
	IF @@ROWCOUNT = 0
	BEGIN
		ROLLBACK
		-- check for valid hash
		IF NOT EXISTS (SELECT * FROM [dbo].[MarkerHash] WHERE [Hash] = @hash)
			RAISERROR (N'Hash is incorrect', 13, 1)
		ELSE
			-- check for expiration
			IF (SELECT ABS(DATEDIFF(day, [Hash], @currentdate))
			FROM
				[dbo].[MarkerHash]
			WHERE
				[Hash] = @hash) >= 1
			RAISERROR (N'Hash is expired. Relogin is needed.', 13, 2)
		
		RAISERROR (N'Unknown error', 13, 3)
		RETURN -1
	END

	SELECT SCOPE_IDENTITY()
	COMMIT

END
GO*/


--------------------------------------------------------------------------------------------------
-- User
--------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertUser]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[InsertUser]
END
GO

CREATE PROCEDURE [dbo].[InsertUser]
(	@TeamID int,
	@Login nvarchar(50),
	@Password nvarchar(50)
	)
AS
BEGIN
BEGIN TRAN
INSERT INTO [dbo].[User]
           ([TeamID]
           ,[Login]
           ,[Password]
)
     VALUES
(	@TeamID,
	@Login,
	@Password)
	SELECT @@IDENTITY
COMMIT TRAN
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateUser]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[UpdateUser]
END
GO

CREATE PROCEDURE [dbo].[UpdateUser]
(	@ID int,
	@TeamID int,
	@Login nvarchar(50),
	@Password nvarchar(50)
)
AS
BEGIN
BEGIN TRAN
UPDATE [dbo].[User] SET
           [TeamID] = @TeamID
           ,[Login] = @Login
           ,[Password] = @Password
WHERE ID = @ID

COMMIT TRAN
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserByID]') AND type in (N'P', N'PC'))
BEGIN
	DROP PROCEDURE [dbo].[GetUserByID]
END
GO

CREATE PROCEDURE [dbo].[GetUserByID] (@ID int)
AS
BEGIN
	SELECT  P.[ID]          
			,P.[TeamID]
           ,P.[Login]
           ,P.[Password]
           ,T.[Name]  as TeamName
 FROM dbo.[User] P
 LEFT JOIN dbo.Team T ON P.TeamID = T.ID
	WHERE P.ID = @ID
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserByID]') AND type in (N'P', N'PC'))
BEGIN
	DROP PROCEDURE [dbo].[GetUserByLogin]
END
GO

CREATE PROCEDURE [dbo].[GetUserByLogin] (@Login nvarchar(50))
AS
BEGIN
	SELECT  P.[ID]          
			,P.[TeamID]
           ,P.[Login]
           ,P.[Password]
           ,T.[Name]  as TeamName
 FROM dbo.[User] P
 LEFT JOIN dbo.Team T ON P.TeamID = T.ID
	WHERE P.Login = @Login
END
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserList]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[GetUserList]
END
GO

CREATE PROCEDURE [dbo].[GetUserList]
AS
BEGIN
	SELECT 
		P.[ID]          
		,P.[TeamID]
        ,P.[Login]
        ,P.[Password]
        ,T.[Name] as TeamName
 FROM dbo.[User] P
 LEFT JOIN dbo.Team T ON P.TeamID = T.ID
END
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteUser]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[DeleteUser]
END
GO

CREATE PROCEDURE [dbo].[DeleteUser] (@ID int)
AS
BEGIN
	DELETE FROM dbo.[User] WHERE ID = @ID
END
GO

--------------------------------------------------------------------------------------------------
-- Team
--------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertTeam]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[InsertTeam]
END
GO

CREATE PROCEDURE [dbo].[InsertTeam]
(	@Name nvarchar(50),
	@Description nvarchar(50)
)
AS
BEGIN
INSERT INTO [dbo].[Team]
           ([Name]
           ,[Description])
     VALUES
(	@Name,
	@Description
)
	SELECT @@IDENTITY
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateTeam]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[UpdateTeam]
END
GO

CREATE PROCEDURE [dbo].[UpdateTeam]
(	@ID int,
	@Name nvarchar(50),
	@Description nvarchar(50))
AS
BEGIN
UPDATE [dbo].[Team] SET
           [Name] = @Name
           ,[Description] = @Description
WHERE ID = @ID
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTeamByID]') AND type in (N'P', N'PC'))
BEGIN
	DROP PROCEDURE [dbo].[GetTeamByID]
END
GO

CREATE PROCEDURE [dbo].[GetTeamByID] (@ID int)
AS
BEGIN
	SELECT  T.ID
			,T.[Name]    
			,T.[Description]
 FROM dbo.Team T
	WHERE T.ID = @ID
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTeamList]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[GetTeamList]
END
GO

CREATE PROCEDURE [dbo].[GetTeamList]
AS
BEGIN
	SELECT  T.ID,
			T.[Name],   
			T.[Description]
 FROM dbo.Team T
END
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteTeam]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[DeleteTeam]
END
GO

CREATE PROCEDURE [dbo].[DeleteTeam] (@ID int)
AS
BEGIN
	DELETE FROM dbo.Team WHERE ID = @ID
END
GO
